import sqlite3

conn = sqlite3.connect('course_database.db')
print("数据库打开成功")
c = conn.cursor()

c.execute('''CREATE TABLE IF NOT EXISTS COURSE
(
    ID          INTEGER PRIMARY KEY                    NOT NULL,
    TITLE       VARCHAR(200)                           NOT NULL,
    DESCRIPTION VARCHAR(500) DEFAULT ''                NOT NULL,
    DURATION    INTEGER      DEFAULT 0                 NOT NULL,
    CREATED_AT  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    UPDATED_AT  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP NOT NULL
);''')

c.execute('''CREATE TABLE IF NOT EXISTS COURSE_ATTACHMENT
(
    ID            INTEGER PRIMARY KEY                    NOT NULL,
    COURSE_ID     INTEGER      DEFAULT 0                 NOT NULL,
    CONTENT       VARCHAR(500) DEFAULT ''                NOT NULL,
    CATEGORY      VARCHAR(20)  DEFAULT ''                NOT NULL,
    "ORDER"       INTEGER      DEFAULT 0                 NOT NULL,
    ATTACHMENT_ID INTEGER      DEFAULT 0                 NOT NULL,
    DURATION      INTEGER      DEFAULT 0                 NOT NULL,
    URL           TEXT         DEFAULT ''                NOT NULL,
    MIME_TYPE     VARCHAR(20)  DEFAULT ''                NOT NULL,
    CREATED_AT    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    UPDATED_AT    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP NOT NULL
);''')

c.execute('''CREATE TABLE IF NOT EXISTS JSON_DATA
(
    ID         INTEGER PRIMARY KEY                   NOT NULL,
    JSON_DATA  TEXT        DEFAULT ''                NOT NULL,
    "TYPE"       VARCHAR(20) DEFAULT ''                NOT NULL,
    CREATED_AT TIMESTAMP   DEFAULT CURRENT_TIMESTAMP NOT NULL,
    UPDATED_AT TIMESTAMP   DEFAULT CURRENT_TIMESTAMP NOT NULL
);''')

print("数据表创建成功")
conn.commit()
conn.close()

'''
{
  "id": 8157,
  "course_id": 488,
  "content": "a03fd311-76c5-4bdf-a01f-6011ea60cd3d.mp3",
  "category": "audio",
  "attachment_id": "a03fd311-76c5-4bdf-a01f-6011ea60cd3d",
  "order": 2,
  "duration": 48290,
  "created_at": "2024-05-23T07:23:23.082Z",
  "updated_at": "2024-05-23T07:24:26.551Z",
  "attachment": {
    "id": 43229,
    "attachment_id": "a03fd311-76c5-4bdf-a01f-6011ea60cd3d",
    "name": "a03fd311-76c5-4bdf-a01f-6011ea60cd3d.mp3",
    "thumb": "",
    "raw": "https://xuexi-courses-storage.firesbox.com/7000102069/replay/a03fd311-76c5-4bdf-a01f-6011ea60cd3d.mp3",
    "size": 386349,
    "duration": 48290,
    "mime_type": "audio/mp3",
    "location": "aws_s3",
    "created_at": "2024-05-23T07:23:23.059Z",
    "updated_at": "2024-05-23T07:24:26.546Z",
    "url": "https://xuexi-courses-storage.firesbox.com/7000102069/replay/a03fd311-76c5-4bdf-a01f-6011ea60cd3d.mp3",
    "raw_url": "https://xuexi-courses-storage.firesbox.com/7000102069/replay/a03fd311-76c5-4bdf-a01f-6011ea60cd3d.mp3",
    "thumb_url": "https://xuexi-courses-storage.firesbox.com/7000102069/replay/a03fd311-76c5-4bdf-a01f-6011ea60cd3d.mp3"
  }
}
'''