CREATE TABLE uk_vat_register(
    id INT IDENTITY(1,1) PRIMARY KEY,
    company_name_pinyin NVARCHAR(255) NULL,
    vat_effective_date_day TINYINT NULL,
    vat_effective_date_month TINYINT NULL,
    vat_effective_date_year SMALLINT NULL,
    legal_person_first_name NVARCHAR(50) NULL,
    legal_person_last_name NVARCHAR(50) NULL,
    legal_person_birthday_day TINYINT NULL,
    legal_person_birthday_month TINYINT NULL,
    legal_person_birthday_year SMALLINT NULL,
    legal_person_id_address1 NVARCHAR(255) NULL,
    legal_person_id_address2 NVARCHAR(255) NULL,
    customer_email NVARCHAR(255) NULL,
    customer_phone VARCHAR(50) NULL,
    office_address1 NVARCHAR(255) NULL,
    office_address2 NVARCHAR(255) NULL,
    customer_sales_platforms NVARCHAR(255) NULL,
    submission_month NVARCHAR(50) NULL,
    upload_file_path1 NVARCHAR(512) NULL,
    upload_file_path2 NVARCHAR(512) NULL,
    upload_file_path3 NVARCHAR(512) NULL,
    mtd_account NVARCHAR(100) NULL,
    mtd_password NVARCHAR(100) NULL,
    mtd_key NVARCHAR(255) NULL,
    created_date DATETIME DEFAULT GETDATE(),
    updated_date DATETIME DEFAULT GETDATE()
); 
ALTER TABLE "dbo"."uk_vat_register"
	ADD "reg_number" NVARCHAR(100) DEFAULT NULL;
-- 添加注册状态字段（默认为 0：待注册）
ALTER TABLE uk_vat_register 
ADD registration_status TINYINT CONSTRAINT DF_uk_vat_register_status DEFAULT 0;

-- 添加客户邮箱密码
ALTER TABLE uk_vat_register ADD customer_email_password NVARCHAR(100) NULL;

-- 添加年度GMV
ALTER TABLE uk_vat_register ADD annual_gmv INT NULL;

-- 添加注册返回编号字段（如 GB123456789）
ALTER TABLE uk_vat_register 
ADD vat_registration_number NVARCHAR(20) NULL;

-- 添加注册失败次数字段（默认为 0）
ALTER TABLE uk_vat_register 
ADD registration_failure_count INT DEFAULT 0;

-- 添加法人国家
ALTER TABLE uk_vat_register 
ADD legal_person_country NVARCHAR(20) NULL;

-- 添加办公地址国家
ALTER TABLE uk_vat_register 
ADD office_country NVARCHAR(20) NULL;

-- 添加年度GMV字段
ALTER TABLE uk_vat_register ADD annual_gmv INT NULL;

CREATE TABLE email_sequence_manager (
	id INT IDENTITY(1,1) PRIMARY KEY,
	sequence_name NVARCHAR(50) NOT NULL UNIQUE,
	current_value INT NOT NULL DEFAULT 0,
	created_date DATETIME DEFAULT GETDATE(),
	updated_date DATETIME DEFAULT GETDATE()
);

-- 插入VAT注册邮箱序号记录
INSERT INTO email_sequence_manager (sequence_name, current_value) 
VALUES ('vat_registration_email', 0);

-- 更新表注释
EXEC sp_updateextendedproperty 
    @name = N'MS_Description', 
    @value = N'UK VAT 注册与 MTD 配置信息表', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register';

-- 更新字段注释
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'公司全名拼音', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'company_name_pinyin';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'VAT生效日期-日', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'vat_effective_date_day';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'VAT生效日期-月', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'vat_effective_date_month';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'VAT生效日期-年', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'vat_effective_date_year';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'法人名字', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'legal_person_first_name';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'法人姓氏', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'legal_person_last_name';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'法人生日-日', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'legal_person_birthday_day';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'法人生日-月', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'legal_person_birthday_month';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'法人生日-年', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'legal_person_birthday_year';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'法人身份证地址1', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'legal_person_id_address1';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'法人身份证地址2', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'legal_person_id_address2';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'客户邮箱地址', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'customer_email';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'客户电话号码', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'customer_phone';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'办公地址1', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'office_address1';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'办公地址2', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'office_address2';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'客户的销售平台和产品', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'customer_sales_platforms';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'提交申报时间（如：December）', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'submission_month';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'上传文件地址1', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'upload_file_path1';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'上传文件地址2', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'upload_file_path2';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'上传文件地址3', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'upload_file_path3';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'MTD账号', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'mtd_account';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'MTD密码', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'mtd_password';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'MTD KEY值', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'mtd_key';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'创建时间', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'created_date';

EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'更新时间', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE', @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'updated_date';


-- 添加注册状态字段（默认为 0：待注册）
ALTER TABLE uk_vat_register 
ADD registration_status TINYINT CONSTRAINT DF_uk_vat_register_status DEFAULT 0;

-- 添加注册返回编号字段（如 GB123456789）
ALTER TABLE uk_vat_register 
ADD vat_registration_number NVARCHAR(20) NULL;

-- 添加注册失败次数字段（默认为 0）
ALTER TABLE uk_vat_register 
ADD registration_failure_count INT DEFAULT 0;

-- 为 registration_status 添加注释
EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'注册状态：0=待注册，1=注册中，2=注册成功，3=注册失败', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE',  @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'registration_status';

-- 为 vat_registration_number 添加注释
EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'HMRC 返回的 VAT 注册编号（如 GB123456789）', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE',  @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'vat_registration_number';

-- 为 registration_failure_count 添加注释
EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'注册失败的累计次数，用于重试控制', 
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE',  @level1name = 'uk_vat_register', 
    @level2type = N'COLUMN', @level2name = 'registration_failure_count';